package renguanyu.app.auth4fx.dao.user;

import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import renguanyu.app.auth4fx.util.DbUtil;
import renguanyu.app.auth4fx.util.Util;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserRoleDao {

    public static Map<String, Object> queryUserRoleList(String roleId){
        String sql = """
        select
        t_role.id as user_id
        ,t_role.name as role_name
        ,group_concat(t_permission.name) as permission_names
        from t_role
        left join t_user_role on t_user_role.user_id = t_role.id and t_user_role.deleted = 'N'
        left join t_permission on t_permission.id = t_user_role.role_id and t_permission.deleted = 'N'
        where t_role.deleted = 'N'
        and t_role.id = ?
        group by role_name
        """;
        return DbUtil.query(sql, new MapHandler(), roleId);
    }

    public static Map<String, Object> queryRole(String roleId){
        String sql = """
        select
        t_role.id as user_id
        ,t_role.name as role_name
        from t_role
        where t_role.deleted = 'N'
        and t_role.id = ?
        """;
        return DbUtil.query(sql, new MapHandler(), roleId);
    }

    /**
     * 查询未拥有的权限列表
     * @param roleId
     * @return
     */
    public static List<Map<String, Object>> queryUnownedRoleList(String roleId){
        List<Object> params = new ArrayList<>();
        String sql = """
        select t_role.*
        from t_role
        where t_role.deleted = 'N'
        and t_role.id not in (
            select
            t_role.id as role_id
            from t_user_role
            left join t_role on t_role.id = t_user_role.role_id
            where t_user_role.deleted = 'N'
            and t_user_role.user_id = ?
        )
        order by updated_at desc
        """;
        params.add(roleId);
        return DbUtil.query(sql.toString(), new MapListHandler(), params.toArray());
    }

    /**
     * 查询已拥有的权限列表
     * @param userId
     * @return
     */
    public static List<Map<String, Object>> queryOwnedRoleList(String userId){
        List<Object> params = new ArrayList<>();
        String sql = """
        select
        t_user_role.id
        ,t_role.id as role_id
        ,t_role.name as role_name
        from t_user_role
        left join t_role on t_role.id = t_user_role.role_id
        where t_user_role.deleted = 'N'
        and t_user_role.user_id = ?
        order by t_user_role.updated_at desc
        """;
        params.add(userId);
        return DbUtil.query(sql.toString(), new MapListHandler(), params.toArray());
    }

    public static int[] saveUserRoleList(Integer size, String userId, List<String> roleIds){
        String sql = """
        INSERT INTO t_user_role
        (id, deleted, created_by, created_at, updated_by, updated_at, user_id, role_id)
        VALUES(?, ?, ?, ?, ?, ?, ?, ?)
        """;
        Object[][] params = new Object[size][8];
        for (int i = 0; i < size; i++) {
            params[i][0] = Util.uuid();
            params[i][1] = "N";
            params[i][2] = Util.getLoginName();
            params[i][3] = Util.getCurrentDateTimeString();
            params[i][4] = Util.getLoginName();
            params[i][5] = Util.getCurrentDateTimeString();
            params[i][6] = userId;
            params[i][7] = roleIds.get(i);
        }
        return DbUtil.batch(sql, params);
    }

    public static int deleteUserRoleList(List<String> ids,String inSql){
        String sql = "UPDATE t_user_role SET deleted=?, updated_by=?, updated_at=? WHERE id IN (" + inSql + ")";
        List<Object> params = new ArrayList<>();
        params.add("Y");
        params.add(Util.getLoginName());
        params.add(Util.getCurrentDateTimeString());
        params.addAll(ids);
        return DbUtil.update(sql, params.toArray());
    }

}
